<?php

/*
 * Copyright (C) 2006 - 2010 Pham Cong Dinh
 *
 * This file is part of Pone.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 3 of
 * the License, or (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
 */

/**
 * A connection (session) with a Oracle database
 *
 * http://code.google.com/p/ganglib/ Horizontal Partitioning strategy
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\oracle
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      November 19, 2008
 * @copyright  Copyright (c) 2006-2009 Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: Connection.php 1735 2010-03-29 03:33:20Z pcdinh $
 */

/**
 * Connection interface
 */
require_once 'library/spica/core/datasource/db/Connection.php';

/**
 * Result set interface
 */
require_once 'library/spica/core/datasource/db/ResultSet.php';

/**
 * @see http://code.google.com/p/maxmods/
 */
class SpicaOracleConnection implements SpicaConnection
{
    /**
     * Database host
     *
     * @var string
     */
    protected $_dbhost;

    /**
     * Database user name
     *
     * @var string
     */
    protected $_dbuser;

    /**
     * Database name
     *
     * @var string
     */
    protected $_db;

    /**
     * Database password associated with the above database user
     *
     * @var string
     */
    protected $_dbpassword;

    /**
     * Database port that database server used
     *
     * @var int
     */
    protected $_dbport = 3306;

    /**
     * Character set we employ for all of the database manipulation operation
     * NLS_LANG='AMERICAN_AMERICA.AL32UTF8'
     *
     * @var string
     */
    protected $_dbcharset = 'AL32UTF8';

    /**
     * Oracle connection flags
     *
     * @var int
     */
    protected $_dbflag = null;

    /**
     * Has the connections closed
     *
     * @var bool
     */
    protected $_isClosed = true;

    /**
     * Is the connection protected from updating, deleting, inserting or replacing data
     *
     * @var bool
     */
    protected $_readOnly = false;

    /**
     * An object of OCI connection that keeps a native database connection to an Oracle server
     *
     * @var resource OCI8 connection
     */
    protected $_connection = null;

    /**
     * An array of open statements that is created by createStatement()
     *
     * @var array
     */
    protected $_openStatements = array();

    /**
     * Is debug mode on?
     *
     * @var bool
     */
    protected $_debugMode = false;

    /**
     * Determine if Oracle database server should commit the action result right after
     * the update/delete/insert query executed or not
     *
     * The default behaviour is to use transaction
     *
     * @var boolean
     */
    protected $_autoCommit = false;

    /**
     * Transaction isolation level.
     *
     * @var string
     */
    protected $_isolationLevel = 'READ COMMITTED';

    /**
     * Has the transaction been started
     *
     * @var boolean
     */
    protected $_transactionStarted = false;

    /**
     * The number of currently open transactions
     *
     * @var int Defaults to 0
     */
    protected $_transactionCount = 0;

    /**
     * Has this connection supported multiple queries sent to server as a single one
     *
     * @var bool
     */
    protected $_allowedMultipleQueries = true;

    /**
     * Has connection been established oci_connect or oci_pconnect?
     *
     * @var bool
     */
    protected $_connectionEstablished = false;

    /**
     * The largest packet we can send (changed once we know what the server supports,
     * we get this at connection init)
     *
     * @var int
     */
    protected $_maxAllowedPacket = 65536;

    /**
     * Enter description here...
     *
     * @var int
     */
    protected $_maxReconnects = 3;

    /**
     * Identify if mixed case unquoted SQL identifiers can be treated as lower case,
     * upper case or mixed case
     *
     * Value can be: SpicaResultSet::IDENTIFIER_LOWERCASE
     *               SpicaResultSet::IDENTIFIER_UPPERCASE
     *               SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @var int
     */
    protected $_sqlIdentifierCase = SpicaResultSet::IDENTIFIER_LOWERCASE;

    /**
     * The function name that is used to connecto the Oracle server.
     * Will be one of two values: oci_connect and oci_pconnect.
     *
     * @var string
     */
    protected $_connectFunction;

    /**
     * Session connection model (Oracle specific)
     * One of the values: OCI_DEFAULT, OCI_SYSOPER, OCI_SYSDBA
     *
     * @var int
     */
    protected $_sessionModel;

    /**
     * Constructs a <code>SpicaOracleConnection</code> object
     *
     * @throws InvalidArgumentException
     * @param  array $params
     * @param  bool  $persistent
     * @return SpicaOracleConnection
     */
    public function __construct($params = array(), $persistent = false)
    {
        if (false === is_array($params) || empty($params))
        {
            throw new InvalidArgumentException('The first parameter to '.__CLASS__.' must be an array that contains database access settings.');
        }

        if (false === isset($params['db']))
        {
            throw new InvalidArgumentException('Class '.__CLASS__.' requires you to set up index "db" in params array.');
        }

        if (false === isset($params['dbuser']))
        {
            throw new InvalidArgumentException('Class '.__CLASS__.' requires you to set up index "dbuser" in params array.');
        }

        if (false === isset($params['dbpassword']))
        {
            throw new InvalidArgumentException('Class '.__CLASS__.' requires you to set up index "dbpassword" in params array.');
        }

        $this->_db         = (string) $params['db'];
        $this->_dbuser     = (string) $params['dbuser'];
        $this->_dbpassword = (string) $params['dbpassword'];

        // Optional
        if (true === isset($params['dbcharset']))
        {
            $this->_dbcharset = (string) $params['dbcharset'];
        }

        // Optional
        if (true === isset($params['dbport']))
        {
            $this->_dbport    = (string) $params['dbport'];
        }

        // Optional
        if (true === isset($params['dbflag']))
        {
            $this->_dbflag    = (int) $params['dbflag'];
        }

        if (true === $persistent)
        {
            $this->_connectFunction = 'oci_pconnect';
        }
        else
        {
            $this->_connectFunction = 'oci_connect';
        }
    }

    /**
     * Gets the SQL identifier case mode.
     *
     * Value can be: SpicaResultSet::IDENTIFIER_LOWERCASE
     *               SpicaResultSet::IDENTIFIER_UPPERCASE
     *               SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return int
     */
    public function getSQLIdentifierCase()
    {
        return $this->_sqlIdentifierCase;
    }

    /**
     * Sets the SQL identifier case mode.
     *
     * @param int $case
     *        + SpicaResultSet::IDENTIFIER_LOWERCASE
     *        + SpicaResultSet::IDENTIFIER_UPPERCASE
     *        + SpicaResultSet::IDENTIFIER_MIXEDCASE
     */
    public function setSQLIdentifierCase($case)
    {
        $this->_sqlIdentifierCase = $case;
    }

    /**
     * Sets this connection's auto-commit mode.
     *
     * By default, Oracle runs with autocommit mode enabled
     *
     * If a connection is in auto-commit mode, then all its SQL statements will
     * be executed and committed as individual transactions
     *
     * Otherwise, its SQL statements are grouped into transactions that are
     * terminated by a call to either the method commit or the method rollback
     *
     * By default, new connections are in auto-commit mode
     *
     * The commit occurs when the statement completes or the next execute occurs,
     * whichever comes first
     *
     * In the case of statements returning a ResultSet, the statement completes
     * when the last row of the ResultSet has been retrieved or the ResultSet has been closed
     *
     * In advanced cases, a single statement may return multiple results as well
     * as output parameter values
     *
     * In these cases the commit occurs when all results and output parameter
     * values have been retrieved
     *
     * @param bool $mode
     */
    public function setAutoCommit($autoCommit)
    {
        $this->_autoCommit = (bool) $autoCommit;
    }

    /**
     * Sets connection timeout.
     *
     * @param int $time Seconds
     */
    public function setTimeout($time)
    {
        // no-op
    }

    /**
     * Sets config file for database server connection.
     *
     * @param string $path
     */
    public function setDefaultServerConfFile($path)
    {
        // no-op; no Oracle equivalent
    }

    /**
     * Attempts to change the transaction isolation level for this SpicaConnection
     * object to the one given.
     *
     * @param int $isolationLevel
     */
    public function setTransactionIsolation($isolationLevel)
    {
        $this->_isolationLevel = $isolationLevel;
    }

    /**
     * Gets low level database connection.
     *
     * @throws SpicaDatabaseException when database connection is closed
     * @return oci resource
     */
    public function getNativeConnection()
    {
        $fresh     = false;
        if (false  === $this->_connectionEstablished)
        {
            $this->_establishConnection();
            $fresh = true;
        }

        if (true   === $fresh)
        {
            return $this->_connection;
        }

        try
        {
            $this->_checkForClosedConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Database connection is closed. ', null, null, null, $ex);
        }

        return $this->_connection;
    }

    /**
     * Sets the charset on the current connection.
     *
     * @param string $charset
     */
    public function setCharset($charset)
    {
        $this->_dbcharset = $charset;
    }

    /**
     * Gets Oracle server client charset endcoding.
     *
     * @see    SpicaConnection#getClientCharset()
     * @return string
     */
    public function getClientCharset()
    {
        // no-op
    }

    /**
     * Registers a Statement instance as open.
     *
     * @see   SpicaConnection#registerStatement()
     * @param SpicaOracleStatement $stmt the SpicaOracleStatement instance to remove
     */
    public function registerStatement($stmt)
    {
        $this->_openStatements[] = $stmt;
    }

    /**
     * Removes the given statement from the list of open statements
     *
     * @see   SpicaConnection#unregisterStatement()
     * @param SpicaOracleStatement $stmt the SpicaOracleStatement instance to remove
     */
    public function unregisterStatement($stmt)
    {
        if (false === empty($this->_openStatements))
        {
            foreach ($this->_openStatements as $pos => $openStmt)
            {
                if ($stmt === $openStmt)
                {
                    unset($this->_openStatements[$pos]);
                    break;
                }
            }
        }
    }

    /**
     * Creates a <code>SpicaOracleStatement</code> object for sending
     * SQL statements to the database
     *
     * SQL statements without parameters are normally executed using SpicaStatement objects
     * If the same SQL statement is executed many times, it may be more efficient
     * to use a SpicaOraclePreparedStatement object
     *
     * @param  int $resultSetType  Result set type which defaults to TYPE_FORWARD_ONLY
     * @param  int $identifierCase Field name case sensitiveness defaults to IDENTIFIER_LOWERCASE
     * @return SpicaOracleStatement
     */
    public function createStatement($identifierCase = null)
    {
        // Checking for closed connection is kept until query execution time
        if (null !== $identifierCase)
        {
            $this->_sqlIdentifierCase = $identifierCase;
        }

        require_once 'library/spica/core/datasource/db/oracle/Statement.php';
        return new SpicaOracleStatement($this);
    }

    /**
     * (non-PHPdoc)
     * @see trunk/libary/spica/datasource/db/Connection#createMultipleQueryStatement()
     */
    public function createMultipleQueryStatement($identifierCase = null)
    {
        throw new SpicaSQLFeatureNotSupportedException('Oracle has not supported this feature yet.');
    }

    /**
     * Creates a SpicaOraclePreparedStatement object for sending
     * parameterized SQL statements to the database
     *
     * A SQL statement with or without IN parameters can be pre-compiled and
     * stored in a SpicaOraclePreparedStatement object
     *
     * This object can then be used to efficiently execute this statement multiple times.
     *
     * @throws SpicaDatabaseException if a database access error occurs
     * @param  string $sql
     * @return SpicaOraclePreparedStatement
     */
    public function prepareStatement($sql)
    {
        // Checking for closed connection is kept until query execution time
        require_once 'library/spica/core/datasource/db/oracle/PreparedStatement.php';
        return new SpicaOraclePreparedStatement($this, $sql);
    }

    /**
     * Creates a statement from a function or stored procedure
     *
     * @param  string $sql
     * @return SpicaOracleCallableStatement
     */
    public function prepareCall($sql)
    {
        // Checking for closed connection is kept until query execution time
        require_once 'library/spica/core/datasource/db/oracle/CallableStatement.php';
        return new SpicaOracleCallableStatement($this, $sql);
    }

    /**
     * Begins a transaction
     *
     * @see   SpicaOracleConnection::_establishConnection()
     * @param string $savepoint A savepoint name
     */
    public function beginTransaction($savepoint = null)
    {
        // Connect to the database if there is no established connection
        $this->_establishConnection();

        // Check if the established connection is active or not
        try
        {
            $this->_checkForClosedConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to begin a transaction because database connection is closed', null, null, $ex);
        }

        $this->_autoCommit = false;

        if (null !== $savepoint)
        {
            $stmt = oci_parse($this->_connection, 'SAVEPOINT '.$savepoint);

            if (false === $stmt)
            {
                $err  = oci_error($this->_connection);
                throw new SpicaDatabaseException('Unable to create a savepoint named '.$savepoint, $err['message'], $err['code'], $err['code']);
            }

            $result   = oci_execute($stmt, OCI_DEFAULT);

            if (false === $result)
            {
                $err  = oci_error($stmt);
                throw new SpicaDatabaseException('Unable to create a savepoint named '.$savepoint, $err['message'], $err['code'], $err['code']);
            }
        }

        $this->_transactionStarted = true;
        $this->_transactionCount++;
    }

    /**
     * Commits a transaction
     *
     * @param string $savepoint A savepoint name
     */
    public function commit($savepoint = null)
    {
        if ($this->_transactionCount > 0)
        {
            try
            {
                $this->_checkForClosedConnection();
            }
            catch (SpicaDatabaseException $ex)
            {
                throw new SpicaDatabaseException('Unable to commit database changes because database connection is closed', null, null, null, $ex);
            }

            // Returns TRUE on success or FALSE on failure.
            $result   = oci_commit($this->_connection);

            if (false === $result)
            {
                throw new SpicaDatabaseException('Unable to commit database changes in the current transaction. ', mysqli_error($this->_connection), mysqli_sqlstate($this->_connection), mysqli_errno($this->_connection));
            }

            $this->_transactionCount--;
        }

        if (0 === $this->_transactionCount)
        {
            $this->_transactionStarted = false;
        }
    }

    /**
     * Cancels any database changes done during a transaction or since a specific
     * savepoint that is in progress
     *
     * This function may only be called when auto-committing is disabled, otherwise it will fail
     * Therefore, a new transaction is implicitly started after cancelling the pending changes
     *
     * @throws SpicaDatabaseException when database connection is closed, when
     * the rollback is unsuccessful
     * @param  string $savepoint A savepoint name
     */
    public function rollback($savepoint = null)
    {
        if (true === $this->_autoCommit)
        {
            throw new SpicaDatabaseException('Rollback operation is not supported when autocommit mode is turned on');
        }

        if ($this->_transactionCount > 0)
        {
            try
            {
                $this->_checkForClosedConnection();
            }
            catch (SpicaDatabaseException $ex)
            {
                throw new SpicaDatabaseException('Unable to rollback database changes because database connection is closed', null, null, null, $ex);
            }

            $openStatementCount = count($this->_openStatements);

            if ($openStatementCount > 0)
            {
                $lastStatement  = $this->_openStatements[$openStatementCount - 1];

                // if the last statement is active
                $active     = false;

                if (true    === is_object($lastStatement))
                {
                    $active = !$lastStatement->isClosed();
                }

                $result   = null;

                if (null !== $savepoint)
                {
                    $stmt     = oci_parse($this->_connection, 'ROLLBACK TO SAVEPOINT '.$savepoint);

                    if (false === $stmt)
                    {
                        $err  = oci_error($this->_connection);
                        throw new SpicaDatabaseException('Unable to parse ROLLBACK TO SAVEPOINT '.$savepoint, $err['message'], $err['code'], $err['code']);
                    }

                    $result   = oci_execute($stmt, OCI_DEFAULT);

                    if (false === $result)
                    {
                        $err  = oci_error($stmt);
                        throw new SpicaDatabaseException('Unable to execute ROLLBACK TO SAVEPOINT '.$savepoint, $err['message'], $err['code'], $err['code']);
                    }
                }
                else
                {
                    $result   = oci_rollback($this->_connection);

                    if (false === $result)
                    {
                        throw new SpicaDatabaseException('Unable to rollback the current transaction', $err['message'], $err['code'], $err['code']);
                    }
                }

                $this->_transactionCount--;
            }
        }

        if (0 === $this->_transactionCount)
        {
            $this->_transactionStarted = false;
        }
    }

    /**
     * @see   SpicaConnection#releaseSavepoint(Savepoint)
     * @param string $savepoint
     */
    public function releaseSavepoint($savepoint)
    {
        // this is a no-op
    }

    /**
     * Gets the current auto-commit state
     *
     * @see    SpicaOracleConnection::setAutoCommit
     * @return bool Current state of auto-commit
     */
    public function getAutoCommit()
    {
        return $this->_autoCommit;
    }

    /**
     * Checks if the connection is closed
     *
     * @return bool
     */
    public function isClosed()
    {
        return $this->_isClosed;
    }

    /**
     * Tests to see if the connection is in Read Only Mode
     *
     * Note that we cannot really put the database in read only mode,
     * but we pretend we can by returning the value of the readOnly flag
     *
     * @return true if the connection is read only
     */
    public function isReadOnly()
    {
        return $this->_readOnly;
    }

    /**
     * Puts this connection in read-only mode as a hint to enable database optimizations
     *
     * Note: This method cannot be called while in the middle of a transaction
     *
     * @param bool $readOnly
     */
    public function setReadOnly($readOnly)
    {
        $this->_readOnly = (bool) $readOnly;
    }

    /**
     * Clones the current connection object <code>SpicaOracleConnection</code>
     *
     * @return SpicaOracleConnection
     */
    public function duplicate()
    {
        return clone $this;
    }

    /**
     * Has this connection supported multiple queries as a single one
     *
     * @return bool
     */
    public function getAllowMultiQueries()
    {
        return false;
    }

    /**
     * Escapes special characters in a string for use in a SQL statement
     *
     * @param  string $value
     * @return string
     */
    public function quote($value)
    {
        return str_replace("'", "''", $value);
    }

    /**
     * Tests if the current connection is in a transaction
     *
     * @return bool
     */
    public function isInTransaction()
    {
        return (bool) ($this->_transactionStarted && !$this->_transactionCount);
    }

    /**
     * Checks if the physical connection to database server is active or not
     *
     * @see    SpicaOracleConnection::_ping()
     * @throws SpicaDatabaseException When the database connection is not active
     */
    protected function _checkForClosedConnection()
    {
        if (true === $this->_isClosed)
        {
            throw new SpicaDatabaseException('The connection to database server is closed. ');
        }
    }

    /**
     * Closes the connection
     */
    public function close()
    {
        $this->_realClose(true, true);
    }

    /**
     * Makes a connection to an Oracle server
     *
     * @throws SpicaDatabaseException when connection can not establish or the given database/schema is unable to access
     */
    protected function _establishConnection()
    {
        if (false === $this->_connectionEstablished)
        {
            // Establish a connection to a Oracle database engine
            $function = $this->_connectFunction;
            $this->_connection = $function($this->_dbuser, $this->_dbpassword, $this->_db, $this->_dbcharset, $this->_sessionModel);

            if (false === $this->_connection)
            {
                $info = oci_error();
                throw new SpicaDatabaseException('Unable to establish database connection. Configuration for database access may be not valid or the database server is not available at the moment. ', $info['code'], $info);
            }

            $this->_connectionEstablished = true;
            $this->_isClosed              = false;
        }
    }

    /**
     * Closes all currently open statement
     */
    protected function closeAllOpenStatements()
    {
        for ($i = 0, $size = count($this->_openStatements); $i < $size; $i++)
        {
            // oci_free_statement doesn't always free up cursors
            oci_cancel($this->_openStatements[$i]);
            oci_free_statement($this->_openStatements[$i]);
        }

        $this->_openStatements = array();
    }

    /**
     * Closes the database connection physically
     *
     * @throws SpicaDatabaseException when a transaction is found to be open (not commit or rollback)
     * @param  bool $calledExplicitly
     * @param  bool $issueRollback
     */
    protected function _realClose($calledExplicitly, $issueRollback)
    {
        if (false === $this->_isClosed)
        {
            if (false === $this->_autoCommit && $issueRollback)
            {
                $this->rollback();
            }
        }

        if (true === $this->_transactionStarted)
        {
            throw new SpicaDatabaseException('There is a transaction that is open previously. You have not executed commit() or rollback() against this transaction yet. ');
        }

        $this->closeAllOpenStatements();
        $this->_openStatements = array();

        if (true === is_resource($this->_connection))
        {
            oci_close($this->_connection);
        }

        $this->_isClosed = true;
    }

    /**
     * Gets application message depending on Oracle error code
     *
     * @param  int $code
     * @return string
     */
    protected function _getMessageByCode($code)
    {
        switch ($code)
        {
            case 2002:
                $message = 'Unable to connect to database server before connection timeout occurs. ';
                break;

            case 1045:
                // error 1045 for access denied for user account
                $message = 'Unable to connect to database server. This either means that the username and password information in the website settings is incorrect or we can\'t contact the Oracle database server through the mysqli libraries. This could also mean the current hosting provider\'s database server is down';
                break;

            default:
                // Find all database connection errors (>= 2000)
                $message = 'Unable to connect to database server. Configuration for database server access may be not valid or the database server is not available at the moment. ';
        }

        return $message;
    }

    /**
     * Destroys the current <code>SpicaOracleConnection</code> object
     */
    public function __destruct()
    {
        // If PHP exits due to a code bug during a transaction, an Oracle table
        // can remain locked until Apache is restarted
        // Release transaction lock
        if (true === $this->_transactionStarted)
        {
            $this->rollback();
        }
    }
}

/**
 * The class providers set of common methods that both static statement and prepared statement share.
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\oracle
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      January 23, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 */
class SpicaOracleCommonStatement extends SpicaCommonStatement
{
    /**
     * Guesses SQL statement type
     *
     * @param string $sql SQL command defaults to null
     * @see   SpicaOraclePreparedStatement::_isSelect
     */
    protected function _guessQueryType($sql = null)
    {
        if (null === $sql)
        {
            $sql = $this->_lastQuery;
        }

        $this->_isSelect = SpicaOracleCommand::validateSelect($sql);
    }
}

/**
 * The class providers set of common methods that both static statement resultset
 * and prepared statement resultset share.
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\oracle
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      January 23, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 */
class SpicaOracleCommonResultSet
{
    /**
     * Native OCI statement handler
     *
     * @var resource OCI statement handler
     */
    protected $_stmt = null;

    /**
     * Is this result available to retrieve from?
     *
     * If it is not, this result set contains an error and carry a message to explain this status
     *
     * @var bool
     */
    protected $_isAvailable = false;

    /**
     * A flag to indicate if the result set is closed
     *
     * @var bool
     */
    protected $_isClosed = false;

    /**
     * The current row that the cursor points to
     *
     * @var int
     */
    protected $_currentCursor = 0;

    /**
     * The total rows in the result set
     *
     * @var int
     */
    protected $_rowCount = null;

    /**
     * The total fields in the result set
     *
     * @var int
     */
    protected $_fieldCount = null;

    /**
     * An associative array that corresponds to the currently fetched row or NULL if there are no more rows
     *
     * @var array|null
     */
    protected $_currentRow = null;

    /**
     * Is this result set be produced by a SELECT or SHOW statement?
     *
     * @var bool
     */
    protected $_isSelect   = true;

    /**
     * All fields names should be in lowercase, upper case or mixed case
     *
     * @var int
     */
    protected $_fieldCase;

    /**
     * All rows in the result set
     *
     * @see SpicaOraclePreparedStatementResultSet::rowCount()
     * @var array
     */
    protected $_rs;

    /**
     * Retrieves all rows in the current result set as a <code>SpicaRowList</code> object
     *
     * @return SpicaRowList
     */
    public function getRowList()
    {
        include_once 'spica/core/utils/ContainerUtils.php';
        return new SpicaRowList($this->getAssociativeArray());
    }

    /**
     * Retrieves XML representation of all rows in the current result set
     *
     * @return string
     */
    public function getXml()
    {
        include_once 'spica/core/utils/FormatUtils.php';
        return SpicaFormatUtils::arrayToXml($this->getAssociativeArray());
    }

    /**
     * Gets a JSON representation of rows contained in the result set
     *
     * @return string
     */
    public function getJson()
    {
        return json_encode($this->getAssociativeArray());
    }

    /**
     * Fetches all rows from the result set as an associative array
     *
     * This method always returns an array. Field names returned by this function are case-sensitive
     *
     * @return array
     */
    public function getAssociativeArray()
    {
        return $this->_getArray(1);
    }

    /**
     * Fetches all rows from the result set as an enumerated array
     *
     * @return array
     */
    public function getNumericArray()
    {
        return $this->_getArray(2);
    }

    /**
     * Determines how many result rows were found by the preceding query
     *
     * Not used for INSERT, DELETE, or UPDATE
     *
     * @return int The number of result rows.
     */
    public function rowCount()
    {
        // Verbose code for performance
        if (null !== $this->_rowCount)
        {
            return $this->_rowCount;
        }

        $this->_checkAvailability();

        if (true === is_resource($this->_stmt))
        {
            $this->_rowCount = oci_fetch_all($this->_stmt, $rs, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
            $this->_rs       = $rs;
        }

        return $this->_rowCount;
    }

    /**
     * Determines how many result fields were found by the preceding query
     *
     * @return int The number of result fields.
     */
    public function fieldCount()
    {
        // Verbose code for performance
        if (null !== $this->_fieldCount)
        {
            return $this->_fieldCount;
        }

        $this->_checkAvailability();

        if (true === is_resource($this->_stmt))
        {
            $this->_fieldCount = oci_num_fields($this->_stmt);
        }

        return $this->_fieldCount;
    }

    /**
     * Moves the cursor to the given row number in this <code>SpicaResultSet</code> object
     *
     * @param  int $row
     * @return bool true if the cursor is on the result set; false otherwise
     */
    public function absolute($row)
    {
        if (false === is_int($row))
        {
            throw new InvalidArgumentException('Method absolute() requires its first parameter to be an integer. ');
        }

        // http://bugs.mysql.com/bug.php?id=38252
        if (0    === $row)
        {
            $row = 1;
        }
        else if ($row < 0)
        {
            $row = $this->rowCount() + $row;
        }

        try
        {
            $this->_checkAvailability();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to move the cursor the offset '.$row.' because the result set is not available or closed', null, null, null, $ex);
        }

        if ($row > $this->rowCount())
        {
            return false;
        }

        $rs    = array();
        $count = oci_fetch_all($this->_stmt, $rs, $row -1, $row, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC);

        if (false === $count)
        {
            return false;
        }

        if ($this->_fieldCase === SpicaResultSet::IDENTIFIER_LOWERCASE)
        {
            $rs = array_change_key_case($rs, CASE_LOWER);
        }

        $this->_currentCursor = $row;
        $this->_currentRow    = $rs;
        return true;
    }

    /**
     * Moves the cursor to the first row in this <code>SpicaResultSet</code> object
     *
     * @return bool
     */
    public function first()
    {
        return $this->absolute(1);
    }

    /**
     * Retrieves the current row number
     *
     * The first row is number 1, the second number 2, and so on.
     *
     * @see    SpicaResultSet#currentRowNumber
     * @return int
     */
    public function currentRowNumber()
    {
        return $this->_currentCursor;
    }

    /**
     * Returns row at the current cursor
     *
     * @see    ResultSet#absolute()
     * @return array
     */
    public function currentRow()
    {
        return $this->_currentRow;
    }

    /**
     * Tests if it is possible to retrieve data from a <code>SpicaResultSet</code> object
     *
     * A <code>SpicaResultSet</code> object is not available to extract data
     * when this object contains an error
     *
     * @return bool
     */
    public function isAvailable()
    {
        return $this->_isAvailable;
    }

    /**
     * Retrieves whether this <code>SpicaResultSet</code> object has been closed
     *
     * A <code>SpicaStatement</code> is closed if the method close has been called on it,
     * or if it is automatically closed
     *
     * @return bool true if this <code>SpicaStatement</code> object is closed;
     *              false if it is still open
     */
    public function isClosed()
    {
        return $this->_isClosed;
    }

    /**
     * Retrieves whether the cursor is on the first row of this
     * <code>SpicaResultSet</code> object
     *
     * @return bool
     */
    public function isFirst()
    {
        return (1 === $this->_currentCursor);
    }

    /**
     * Retrieves whether the cursor is on the last row of this <code>SpicaResultSet</code> object
     *
     * @return bool
     */
    public function isLast()
    {
        return ($this->rowCount() === $this->_currentCursor);
    }

    /**
     * Moves the cursor to the last row in this <code>SpicaResultSet</code> object
     *
     * @return bool
     */
    public function last()
    {
        $this->_currentCursor = $this->_rowCount;
        return $this->absolute(($this->_rowCount));
    }

    /**
     * Moves the cursor to the previous row in this <code>SpicaResultSet</code> object
     *
     * @return bool
     */
    public function previous()
    {
        if (0 >= $this->_currentCursor)
        {
            return false;
        }

        $cursor = $this->_currentCursor - 1;

        if (0 > $cursor)
        {
            return false;
        }

        return $this->absolute($cursor);
    }

    /**
     * Moves the cursor forward one row from its current position
     *
     * @return bool
     */
    public function next()
    {
        if (0 === $this->rowCount())
        {
            return false;
        }

        if ($this->_rowCount < $this->_currentCursor + 1)
        {
            return false;
        }

        $this->absolute($this->_currentCursor + 1);

        if (null === $this->_currentRow)
        {
            return false;
        }

        return true;
    }

    /**
     * Releases this <code>SpicaResultSet</code> object's database
     * and resources immediately instead of waiting for this to happen when it is automatically closed
     */
    public function close()
    {
        $this->_realClose(true);
    }

    /**
     * Gets result set as an array
     *
     * @param  int $arrayType 1: Associative, 2: Numeric/Enumerated
     * @return array
     */
    protected function _getArray($arrayType)
    {
        if (null !== $this->_rs)
        {
            if ($this->_fieldCase === SpicaResultSet::IDENTIFIER_LOWERCASE)
            {
                return $this->_lowerKeyCase($this->_rs);
            }
        }

        $this->_checkAvailability();

        if (1 === $arrayType)
        {
            $fetchMode = OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC;
        }
        else
        {
            $fetchMode = OCI_FETCHSTATEMENT_BY_ROW+OCI_NUM;
        }

        $rs = array();
        // Fetches all rows of result data into an sssociative array
        $this->_rowCount = oci_fetch_all($this->_stmt, $rs, 0, -1, $fetchMode);

        if (false === $this->_rowCount)
        {
            $err  = oci_error($this->_stmt);
            throw new SpicaDatabaseException('Unable to fetch data into a result set', $err['message'], $err['code'], $err['code']);
        }

        if ($this->_fieldCase === SpicaResultSet::IDENTIFIER_LOWERCASE)
        {
            $this->_rs = $rs;
            return $this->_lowerKeyCase($rs);
        }

        $this->_rs = $rs;
        return $rs;
    }

    /**
     * Lowers the case of the field names of the result set retrieved from the query
     * Oracle behaviour in this case is compliant with the SQL standard
     * so it always returns the field names in upper case.
     * It is not the case with MySQL and PostgreSQL
     *
     * In PHP perspective, I think that lowercase field names is most relevant because
     * all upper case names are conventionally realized as PHP constant. If you dont like
     * this behaviour you should call $this->setSQLIdentifierCase(ResultSet::IDENTIFIER_UPPERCASE)
     * before executing select query.
     *
     * @see    PHPVietnam Coding Standard for more details
     * @param  array $rs
     * @return array Result set array with all the field name is lower-cased
     */
    protected function _lowerKeyCase($rs)
    {
        // 2-dim array
        if (true === isset($rs[0]) && true === is_array($rs[0]))
        {
            for ($i = 0, $length = count($rs); $i < $length; $i++)
            {
                $rs[$i] = array_change_key_case($rs[$i], CASE_LOWER);
            }

            return $rs;
        }

        return array_change_key_case($rs, CASE_LOWER);
    }

    /**
     * Tests the availability of the result set to extract data from.
     *
     * @throws SpicaDatabaseException
     */
    protected function _checkAvailability()
    {
        if (true === $this->_isClosed)
        {
            throw new SpicaDatabaseException('Unable to extract data from a closed result set. ');
        }

        if (false === $this->_isAvailable)
        {
            throw new SpicaDatabaseException('Unable to extract data from
            a result set that is produced by an errornous statement. ');
        }
    }

    /**
     * Releases this <code>SpicaResultSet</code> object's database
     *
     * @param bool $calledExplicitly
     */
    protected function _realClose($calledExplicitly = false)
    {
        $this->_currentCursor = 0;
        $this->_currentRow    = null;
        $this->_rowCount      = null;
        $this->_fieldCount    = null;
        $this->_isClosed      = true;
        $this->_rs            = null;
        $this->_isAvailable   = false;
    }
}

/**
 * This class provides a set of utility method to work with MySQL dialect SQL commands
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\oracle
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      February 28, 2009
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: Connection.php 1735 2010-03-29 03:33:20Z pcdinh $
 */
class SpicaOracleCommand extends SpicaDatabaseCommand
{
    /**
     * List of string quoting symbols used in Oracle
     *
     * @var array
     */
    public static $stringQuotes;

    /**
     * List of field name quoting symbols used in Oracle
     *
     * @var array
     */
    public static $identifierQuotes;

    /**
     * List of SQL commenting symbols used in Oracle
     *
     * @see SpicaOracleCommand::skipComments
     * @var array
     */
    public static $sqlComments = array(
    array('start' => '-- ', 'end' => "\n", 'escape' => false),
    array('start' => '/*', 'end' => '*/', 'escape' => false)
    );

    /**
     * Returns the first offset of a query that is not within a SQL comment
     *
     * @param  string $sql
     * @param  int    $position
     * @return int
     */
    public static function skipComments($sql, $position = 0)
    {
        return self::_skipBlocks($sql, $position, self::$sqlComments);
    }

    /**
     * Adds an driver-specific LIMIT clause to the query
     *
     * @param  string $query  query to modify
     * @param  int    $limit  limit the number of rows
     * @param  int    $offset start reading from given offset. Offset can be zero if you want to fetch the first row
     * @return string The modified query
     */
    public static function modifyLimitQuery($query, $limit = null, $offset = null)
    {
        if (0 === stripos(trim($query), 'SELECT', 0))
        {
            if ($limit > 0)
            {
                $max    = $offset + $limit;

                if ($offset > 0)
                {
                    $query = 'SELECT * FROM
                                (SELECT a.*, ROWNUM row_identifier
                                 FROM (' . $query. ') a
                                 WHERE ROWNUM <= ' . $max . ')
                              WHERE row_identifier >= ' . ($offset + 1); // Offset can be zero but ROWNUM starts from 1 (pcdinh).
                }
                else
                {
                    $query = 'SELECT a.* FROM (' . $query .') a WHERE ROWNUM <= ' . $max;
                }
            }
        }

        return $query;
    }

    /**
     * Creates a WHERE phrase from an array or a string
     *
     * @see    SpicaOracleStatement::delete()
     * @see    SpicaOracleStatement::update()
     * @param  array|string $where
     * @param  SpicaConnection $conn
     * @return string|false
     */
    public static function buildWherePhrase($where, $conn)
    {
        if (true  === is_array($where))
        {
            $cond = array();
            foreach ($where as $field => $value)
            {
                if (null    === $value)
                {
                    $cond[] = $field . ' IS NULL';
                    continue;
                }

                // Check quoted string. If you dont quote your string, we understand that you will keep it as is
                if (false   === $firstQuoted = strpos($value, "'"))
                {
                    // Build a string like: id > 3
                    $cond[] = $field . ' ' . $value;
                }
                else
                {
                    // Build a string like: id = 'anc \'s \" \\'
                    // Only quoted strings will need real escape (need some thought of XSS/SQL Injection here)
                    // preg_replace_callback can be used here but do you think that it is too complicated for such a simple thing
                    $cond[] = '' . $field . ' ' . substr($value, 0, $firstQuoted)."'".$conn->quote(substr($value, $firstQuoted + 1, strlen($value) - 1))."'";
                }
            }

            $where = '';

            if (count($cond) > 0)
            {
                $where  = ' WHERE '.implode(' AND ', $cond);
                unset($cond);
            }
        }
        elseif (strlen(trim($where)) >= 3) // i=2
        {
            $where = ' WHERE '.$where;
        }
        else
        {
            $where = false;
        }

        return $where;
    }

    /**
     * Checks if a SQL statement is a SELECT one
     *
     * @todo   It is not a reliable algorithm. Looking for a new way to do it
     * @param  string $sql
     * @return bool
     */
    public static function validateSelect($sql)
    {
        switch (strtolower(substr($sql, 0, 3)))
        {
            case 'sel': // SELECT
            case 'sho': // SHOW
            case 'exp': // EXPLAIN
            case 'des': // DESCRIBE (not DELETE)
                return true;
                break;

            case 'cre': // CREATE TABLE ... AS SELECT
                return !(false !== stripos($sql, 'create table') && false !== stripos($sql, 'as select'));

            default:
                return false; // DROP, INSERT, UPDATE, DELETE
        }
    }
}

?>